package com.ffzx.commerce.framework.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.ffzx.commerce.framework.common.persistence.BaseEntity;
import com.ffzx.commerce.framework.exception.ServiceException;
import com.ffzx.commerce.framework.page.Page;
import com.ffzx.commerce.framework.service.BaseCrudService;


/**
 * @ClassName ExcelExportUtil
 * @Description 导出Excel工具类
 * @author Alvin
 * @date 2016年4月20日
 */
public class ExcelExportUtil {
	private static Logger logger=Logger.getLogger(ExcelExportUtil.class);
	
	/**
	 * 定义超过（10M）时压缩文件
	 */
	private final static long TO_ZIP = 10485760;
	private final static int MAXCOUNT = 100000;//定义超过10W条数据，就分sheet
	private final static int PAGESIZE = 20000;//定义每一次最多查询2W条
	/**
	 * 导出Excel，可以放自定义信息的
	 * @param headers 列头
	 * @param properties 字段
	 * @param totalCells 自定义行
	 * @param data 
	 * @param os
	 * @throws IOException
	 */
	private static File export(String[] headers,String[] properties,String[] totalCells,List data,int type,BaseCrudService bcs,Map<String, Object> info){
		FileOutputStream o = null;
		File file = null;
		try { //创建工作薄
			Workbook workBook = null;
			String filepath = System.getProperty("java.io.tmpdir") + File.separatorChar + System.currentTimeMillis()+""+ new Random().nextInt();
			if(type ==1){
				workBook = new HSSFWorkbook();
				filepath += ".xls";
			}else{
				workBook = new SXSSFWorkbook(200);//超过两百行放入就放入磁盘，否则在内存中	
				filepath += ".xlsx";
			}
			file = new File(filepath);
			if(headers == null || headers.length == 0 || properties == null || properties.length == 0 ){
				throw new Exception("入参出错！");
			}
			
			readInRows(workBook, properties,headers, data, bcs, info);
			o = new FileOutputStream(file);  
			workBook.write(o);  
			
        } catch (Exception ex) {  
        	logger.error("导出时构造文件出错："+ex.getMessage());
        } finally {  
            try {
				o.flush();
			} catch (IOException e) {
				e.printStackTrace();
			}
            try {
				o.close();
			} catch (IOException e) {
				e.printStackTrace();
			} 
        }
		
		return file;
	}
	
	/**
	 * 插入数据，返回最大行数
	 * @param sheet
	 * @param properties
	 * @param data
	 * @return
	 */
	private static int setRowData(Sheet sheet,int rowIndex,String[] properties,List<Object> data){
		int i = rowIndex ;
		for(Object bean : data){
			//创建行
			Row row = sheet.createRow(i);
			//遍历属性创建单元格
			int j = 0 ;
			for(String property : properties){
				try {
					Cell cell = row.createCell(j);
					String cellValue = "" ;
					
					if(bean instanceof BaseEntity){
						if(property.indexOf(".")>0){
							String[] params=property.split("\\.");
							Method getMethod =null;
							Object value=null;
							for(int n=0;n<params.length;n++){
								 if(n==0){
									 getMethod = bean.getClass().getMethod(getMethodName("get",params[n])); 
									 value = getMethod.invoke(bean);
								 }else if(n<params.length-1){
									 getMethod = value.getClass().getMethod(getMethodName("get",params[n])); 
									 value = getMethod.invoke(value);
								 } 
							} 
							if(value!=null){
								cellValue=getValueByProperty(value, params[params.length-1]);
							}
						}else{
							cellValue=getValueByProperty(bean, property);
						}
					}else{
						@SuppressWarnings("unchecked")
						Map<String,Object> map = (Map<String,Object>)bean;
						Object v = map.get(property);
						cellValue = (v==null?"":StringUtil.trimToEmpty(String.valueOf(v)));
					}
					
					cell.setCellValue(cellValue);
				} catch (Exception e) {
					logger.error("", e);
				} 
				j ++ ;
			}
			i++ ;
		}
		
		return i;
	}
	
    private static String getValueByProperty(Object obj,String property) throws Exception{
    	String val="";
    	String pp=property.indexOf("[")>0?property.split("\\[")[0]:property;
    	String fmt=property.indexOf("[")>0?property.split("\\[")[1].replace("]", ""):""; 
    	
    	Method getMethod = obj.getClass().getMethod(getMethodName("get",pp));
		//获取方法返回类型
		Object value = getMethod.invoke(obj);
		if(value!=null){
			if(value instanceof Date){
				Date dv = (Date)value ;
				if(StringUtil.isEmpty(fmt)){ 
					val = DateUtil.formatDateTime(dv);
				}else{
					SimpleDateFormat format=new SimpleDateFormat(fmt);
					val=format.format(dv); 
				}
			}else if(value instanceof Enum){
				Method enumGetMenthod = value.getClass().getMethod("getName");
				Object enumValue = enumGetMenthod.invoke(value);
				val = enumValue == null ? "" : enumValue.toString();
			}else if(!StringUtil.isEmpty(fmt) && "phonefmt".equals(fmt) ){ //格式化电话号码 
				if(value.toString().length()>=8){
					val=value.toString().replace(value.toString().substring(3,8),"****");
				}else{
					val = value.toString();
				}
			}else{
				val = value.toString();
			}
		}else{
			val = "" ;
		}
    	
    	return val;
    }
	
	/**
	 * 创建列头
	 * @param sheet
	 * @param headers
	 */
	private static void setHeadRow(Workbook workBook,Sheet sheet , String[] headers){
		// 创建第一行
		CellStyle style = workBook.createCellStyle();
		style.setFillForegroundColor((short)17);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND); 
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框   
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框   
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框  
		
		Row row = sheet.createRow(0);
		int i = 0 ;
		for(String head : headers){
			Cell cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(head);
			i++ ;
		}
	}
	
	/**
	 * 创建汇总行
	 * @param sheet
	 * @param headers
	 */
	private static void setTotalRow(Sheet sheet , String[] totalCells ,List data){
		// 创建行
		Row row = sheet.createRow(data.size()+2);
		int i = 0 ;
		for(String total : totalCells){
			Cell cell = row.createCell(i);
			cell.setCellValue(total);
			i++ ;
		}
	}
	
    /**
     * @Description 根据属性构建get/set方法名
     * @param type
     * @param fildeName
     * @return
     */
    private static String getMethodName(String type,String fildeName) {
    	//把一个字符串的第一个字母大写、效率是最高的、
        byte[] items = fildeName.getBytes();  
        if(items.length > 2 && Character.isUpperCase(items[1])){
        	return type + fildeName ;
        }
        items[0] = (byte) ((char) items[0] - 'a' + 'A');  
        return type + (new String(items));  
    } 
    
    /**
     * 导出数据为一个excel<br>
	 * 如果List中存在的是BaseEntity的子对象，那么直接依赖属性的getXX方法取值(支持.符号  多少个点都行)，适合单表<br>
	 * 如果List中不是BaseEntity，那就必须使用Map来存取，properties中的值用作Map的key，适合业务报表导出
     * @param em
     * @param request
     * @param response
     * @param fileName 导出的文件名
     * @throws IOException
     */
    public static void exportXls(ExportModel em,HttpServletResponse response) throws IOException{
    	File file = export(em.getHeaders(), em.getProperties(), null, em.getData(),1,null,null);
    	down(response, file,em.getFileName()); 
     }

    public static void exportXls(ExportModel em,HttpServletRequest request,HttpServletResponse response,BaseCrudService bcs,Map<String, Object> info) throws IOException{
    	File file = export(em.getHeaders(), em.getProperties(), null, em.getData(),1,null,null);
    	down(response, file,em.getFileName()); 
    	
    }
    
	public static void down(HttpServletResponse response,File file,String downFileName) {
		OutputStream out = null; 
    	try {  
    		
    		String suffix = file.getName().substring(file.getName().lastIndexOf("."));
    		FileInputStream inStream = null;
    		if(file.length() > TO_ZIP){
    			//压缩到临时目录去
        		String filePath = file.getAbsolutePath();
            	String srcDirName = filePath.substring(0, filePath.lastIndexOf(File.separatorChar+""));
            	if(StringUtil.isBlank(downFileName))
            	downFileName = System.currentTimeMillis()+"";
            	downFileName += ".zip";
            	suffix= "";
        		String zipPath = System.getProperty("java.io.tmpdir") + File.separatorChar + downFileName;
            	FileUtils.zipFiles(srcDirName, file.getName(), zipPath);
            	FileUtils.deleteFile(filePath);//删除excel
            	
            	inStream = new FileInputStream(new File(zipPath));
    		}else{
    			inStream = new FileInputStream(file);
    		}
    		response.setContentType("application/octet-stream");
    		response.addHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(downFileName + suffix, "UTF-8"));
            out = response.getOutputStream();  
            
    		byte[] buf = new byte[4096];
            int readLength;  
            while (((readLength = inStream.read(buf)) != -1)) {  
                out.write(buf, 0, readLength);  
            }  
            inStream.close();
        } catch (IOException e) {  
            logger.error("导出文件出错！");  
        } finally {  
            try {  
                out.flush();  
                out.close();  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }
	}
    
   /**
    * 导出2007以上版本,依据传入的BaseCrudService的具体实现findByPage，来分页查询，分sheet存储<br>
    * 当数据量大时压缩下载
    * @param em
    * @param request
    * @param response
    * @param bcs
    * @param info 存放查询条件 （Map）params、orderByField字段、orderBy字段
    * @throws IOException
    */
    public static void exportXlsx(ExportModel em,HttpServletResponse response,BaseCrudService bcs,Map<String, Object> info) throws IOException{
    	File file = export(em.getHeaders(), em.getProperties(), null, em.getData(),2,bcs,info);
    	down(response, file,em.getFileName());
    }
    /**
     * 导出2007以上版本,依据传入的list构造数据
     * @param em
     * @param request
     * @param response
     * @throws IOException
     */
    public static void exportXlsx(ExportModel em,HttpServletResponse response) throws IOException{
    	File file = export(em.getHeaders(), em.getProperties(), null, em.getData(),2,null,null);
    	down(response, file,em.getFileName());
    }
    
    private static void readInRows(Workbook workBook,String[] properties,String[] headers,List data,BaseCrudService bcs,Map<String, Object> info){
    	try {
    		Sheet sheet = null;
    		
    		if(data !=null && data.size()>0){
    			sheet = workBook.createSheet();
    			setHeadRow(workBook,sheet,headers);
    			setRowData(sheet,1, properties, data);
    		}else{
    			if(info == null)
    				info = new HashMap<String,Object>();
    			Object obj = info.get("params");
        		Map<String, Object> params = obj == null ? new HashMap<String,Object>():(Map<String, Object>)obj;
        		
    			int count = 0;
    			int sheetCount = 0;

    			String orderByField = StringUtil.isNotNull(info.get("orderByField"))?String.valueOf(info.get("orderByField")):"";
    			String orderBy = StringUtil.isNotNull(info.get("orderBy"))?String.valueOf(info.get("orderBy")):"";
    			Page page = new Page();
    			page.setPageSize(PAGESIZE);
    			page.setPageIndex(1);
    			
    			int rowIndex = 1;
    			
	    		do {
	    			sheet = workBook.createSheet();
    				setHeadRow(workBook,sheet,headers);
    				//读内容
    				int icount = 0;
    				
        			do{
        				List<Object> listData = bcs.findByPage(page, orderByField, orderBy, params);
        				count = page.getTotalCount();
        				if(sheetCount == 0)
        					sheetCount = (count % MAXCOUNT)>0?((count / MAXCOUNT) +1):(count / MAXCOUNT);
        				page.setPageIndex(page.getPageIndex() + 1);
        				rowIndex = setRowData(sheet,rowIndex, properties, listData);
        				if(icount >= MAXCOUNT){
        					icount = MAXCOUNT;
        				}else{
        					icount += listData.size();
        				}
        			}while(icount < MAXCOUNT && sheetCount>0);
        			rowIndex = 1;
        			sheetCount --;
				} while (sheetCount>0);
    			
    		}
		} catch (ServiceException e) {
			logger.error("--"+e.getMessage());
		}
    }
    /**
     * 封装导出数据
     * 
     * @author Alvin
     * @date 2016年4月20日
     * @version 1.0
     */
    public interface ExportModel{
    	/**
    	 * 数据集合
    	 * @return
    	 */
    	public List getData();
    	/**
    	 * 表头组
    	 * @return
    	 */
    	public String[] getHeaders();
    	/**
    	 * 字段组
    	 * @return
    	 */
    	public String[] getProperties();
    	
    	/**
    	 * 导出文件名
    	 */
    	public String getFileName();
    }
    
    public static class DefaultExportModel<T> implements ExportModel{
    	private String[] headers;
    	private String[] properties;
    	private String fileName;
    	private List<T> data;
    	
    	
		@Override
		public List<T> getData() {
			return data;
		}

		@Override
		public String[] getHeaders() {
			return headers;
		}

		@Override
		public String[] getProperties() {
			return properties;
		}

		@Override
		public String getFileName() {
			return fileName;
		}

		public void setHeaders(String[] headers) {
			this.headers = headers;
		}

		public void setProperties(String[] properties) {
			this.properties = properties;
		}

		public void setFileName(String fileName) {
			this.fileName = fileName;
		}

		public void setData(List<T> data) {
			this.data = data;
		}
		
		
    	
    }
    
}
